package org.claros.intouch.contacts.controllers;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.claros.commons.auth.models.AuthProfile;
import org.claros.commons.db.DbConfigList;
import org.claros.commons.exception.NoPermissionException;
import org.claros.intouch.common.utility.Constants;
import org.claros.intouch.common.utility.Utility;
import org.claros.intouch.contacts.models.ContactGroup;
import org.claros.intouch.contacts.models.ContactGroupObject;

import com.jenkov.mrpersister.impl.mapping.AutoGeneratedColumnsMapper;
import com.jenkov.mrpersister.itf.IGenericDao;
import com.jenkov.mrpersister.itf.mapping.IObjectMappingKey;
import com.jenkov.mrpersister.util.JdbcUtil;

/**
 * @author Umut Gokbayrak
 */
public class GroupsController {
	
	/**
	 * 
	 * @param auth
	 * @return
	 * @throws Exception
	 */
	public static List getGroupsByUser(AuthProfile auth) throws Exception {
		IGenericDao dao = null;
		List groups = null;
		try {
			dao = Utility.getDbConnection();
			String username = auth.getUsername();
			String sql = "SELECT * FROM CONTACT_GROUPS WHERE USERNAME=?";
			groups = dao.readList(ContactGroup.class, sql, new Object[] {username});
		} finally {
			JdbcUtil.close(dao);
			dao = null;
		}
		return groups;
	}
	
	/**
	 * 
	 * @param auth
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public static List getGroupItemsByGroup(AuthProfile auth, Long id) throws Exception {
		IGenericDao dao = null;
		List items = null;
		try {
			dao = Utility.getDbConnection();
			String username = auth.getUsername();
			String sql = "SELECT * FROM CONTACT_GROUP_OBJECTS WHERE USERNAME=? AND GROUP_ID = ?";
			items = dao.readList(ContactGroupObject.class, sql, new Object[] {username, id});
		} finally {
			JdbcUtil.close(dao);
			dao = null;
		}
		return items;
	}

	/**
	 * 
	 * @param auth
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public static Integer getMemberCountByGroup(AuthProfile auth, Long id) throws Exception {
		QueryRunner run = new QueryRunner(DbConfigList.getDataSourceById("file"));
		HashMap result = null;
		String username = auth.getUsername();
		try {
			String sql = "SELECT COUNT(*) AS NUMBER FROM CONTACT_GROUP_OBJECTS WHERE USERNAME=? AND GROUP_ID = ? ";
			result = (HashMap)run.query(sql, new Object[] {username, id}, new MapHandler());
		} catch (SQLException e) {
			return new Integer(0);
		}
		if (result != null) {
			return new Integer(result.get("number").toString());
		}
		return new Integer(0);
	}

	/**
	 * @param auth
	 * @param long1
	 */
	public static void deleteGroups(AuthProfile auth, Long id[]) throws Exception {
		if (id != null) {
			Long tmp = null;
			ContactGroup cgTmp = null;
			String sql = null;
			for (int i=0; i<id.length; i++) {
				tmp = id[i];
				cgTmp = getGroupById(auth, tmp);
				if (!cgTmp.getUsername().equals(auth.getUsername())) {
					throw new NoPermissionException();
				}
				
				IGenericDao dao = null;
				try {
					dao = Utility.getDbConnection();
					sql = "DELETE FROM CONTACT_GROUP_OBJECTS WHERE USERNAME = ? AND GROUP_ID = ?";
					dao.executeUpdate(sql, new Object[] {auth.getUsername(), tmp});
					dao.deleteByPrimaryKey(ContactGroup.class, tmp);
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					JdbcUtil.close(dao);
					dao = null;
				}
			}
		}
	}

	/**
	 * @param auth
	 * @param tmp
	 * @return
	 */
	public static ContactGroup getGroupById(AuthProfile auth, Long id) throws Exception {
		IGenericDao dao = null;
		ContactGroup result = null;
		try {
			dao = Utility.getDbConnection();
			result = (ContactGroup)dao.readByPrimaryKey(ContactGroup.class, id);

			if (!result.getUsername().equals(auth.getUsername())) {
				throw new NoPermissionException();
			}
		} finally {
			JdbcUtil.close(dao);
			dao = null;
		}
		return result;
	}

	/**
	 * @param auth
	 * @param string
	 * @param strings
	 */
	@SuppressWarnings("deprecation")
	public static void addGroup(AuthProfile auth, String groupName, String[] members) throws Exception {
		IGenericDao dao = null;
		try {
			dao = Utility.getDbConnection();

			ContactGroup grp = new ContactGroup();
			grp.setShortName(groupName);
			grp.setDescription("");
			grp.setUsername(auth.getUsername());

			IObjectMappingKey myObj = Constants.persistMan.getObjectMappingFactory().createInstance(ContactGroup.class, new AutoGeneratedColumnsMapper(true));
			dao.insert(myObj, grp);
			
			// find the group id for the previous insertion
			String sql = "SELECT * FROM CONTACT_GROUPS WHERE USERNAME = ? AND SHORT_NAME = ? ORDER BY ID DESC";
			List added = dao.readList(ContactGroup.class, sql, new Object[] {auth.getUsername(), groupName});
			if (added != null && added.size() > 0) {
				ContactGroup tmp = (ContactGroup)added.get(0);
				Long id = tmp.getId();
				
				// add the members to the items list
				if (members != null) {
					Long t = null;
					IObjectMappingKey myObj2 = Constants.persistMan.getObjectMappingFactory().createInstance(ContactGroupObject.class, new AutoGeneratedColumnsMapper(true));
					for (int i=0; i<members.length; i++) {
						try {
							t = new Long(members[i]);
							ContactGroupObject item = new ContactGroupObject();
							item.setUsername(auth.getUsername());
							item.setGroupId(id);
							item.setContactId(t);
							dao.insert(myObj2, item);
						} catch (Exception e) {
							// do nothing sier
						}
					}
				}
			}
		} finally {
			JdbcUtil.close(dao);
			dao = null;
		}
	}

	/**
	 * @param auth
	 * @param strAdr
	 * @return
	 */
	public static ContactGroup searchByName(AuthProfile auth, String strAdr) throws Exception {
		IGenericDao dao = null;
		ContactGroup group = null;
		try {
			dao = Utility.getDbConnection();
			String username = auth.getUsername();
			strAdr = strAdr.toUpperCase(new Locale("en", "US"));
			String sql = "SELECT * FROM CONTACT_GROUPS WHERE USERNAME=? AND UPPER(SHORT_NAME) = ?";
			group = (ContactGroup)dao.read(ContactGroup.class, sql, new Object[] {username, strAdr});
		} finally {
			JdbcUtil.close(dao);
			dao = null;
		}
		return group;
	}

	/**
	 * @param username
	 * @param name
	 * @return
	 */
	public static List searchByLikeName(String username, String name) throws Exception {
		IGenericDao dao = null;
		List groups = null;
		try {
			dao = Utility.getDbConnection();
			name = name.toUpperCase(new Locale("en", "US"));
			String sql = "SELECT * FROM CONTACT_GROUPS WHERE USERNAME=? AND UPPER(SHORT_NAME) LIKE '%" + name + "%'";
			groups = dao.readList(ContactGroup.class, sql, new Object[] {username});
		} finally {
			JdbcUtil.close(dao);
			dao = null;
		}
		return groups;
	}
}
